#! /usr/bin/env python
#coding=utf-8

from uliweb import expose
from sqlalchemy import *
from uliweb.orm import *
from uliweb.utils.pyini import Ini
from sqlalchemy.sql import select
from sqlalchemy.sql import and_, or_, not_

import time, sys
sys.path.insert(0, '../apps')
#from departments.models import *
#from charts.models import *
#from charts.models import *

import datetime
from datetime import date
from get_month_quarter import *

#db = get_connection('sqlite:///../database.db', debug=False)
#调用get_month_quarter.py中get_db_connection()获取数据库联接
db = get_db_connection()

#meta = MetaData(db)
#db.metadata.reflect()


def run():
    #从settings.ini获取任务类型编号
    settings = Ini('../apps/settings.ini')

    assignment_type = dict(settings.PARA.ASSIGNMENT_TYPE)


    req = db.metadata.tables['assignment']
    #任务明细表数据处理,后续根据任务明细表实际表名修改

    #req_mx = db.metadata.tables['assignment_mx']


    #获取本月月初和月末时间
    month_first_day,month_last_day,month_view = get_month()


    #print month_last_day
    #计算本月新增准开发需求任务数，算法：开始日期小于或等于本月末and大于等于本月1日
    #new = select([req.c.kfzx,req.c.type,func.count(req.c.ass_id)]).group_by(req.c.kfzx)
    #统计项目延迟情况数
    yc = select([func.count(func.distinct(func.substr(req.c.ass_id,1,func.instr(req.c.ass_id,'_')))),req.c.kfzx,req.c.type],and_(req.c.status == '2')).group_by(req.c.kfzx,req.c.type)
    #统计项目异常终止情况数
    yczz = select([func.count(func.distinct(func.substr(req.c.ass_id,1,func.instr(req.c.ass_id,'_')))),req.c.kfzx,req.c.type],and_(req.c.status == '4')).group_by(req.c.kfzx,req.c.type)
    #统计项目暂停情况数
    zt = select([func.count(func.distinct(func.substr(req.c.ass_id,1,func.instr(req.c.ass_id,'_')))),req.c.kfzx,req.c.type],and_(req.c.status == '5')).group_by(req.c.kfzx,req.c.type)


    #统计续建任务中任务状态在本月修改为完成或异常终止的任务



    yc_rs = yc.execute()
    yczz_rs = yczz.execute()
    zt_rs = zt.execute()


    #定义字典型data
    data = {}
    #连接kfzx表,根据开发中心id初始化data
    types = ['1','2','3','4']
    req_tx_model = get_model('kfzx')
    sel_tx = select([req_tx_model.c.id])


    for x in types:
    #    print x
        data[x] ={}
        tx_dic = sel_tx.execute()
        for stages in tx_dic:
            tx, = stages
            y = 'yc_num_%02d' % tx
            data[x][y] = 0
            y = 'yczz_num_%02d' % tx
            data[x][y] = 0
            y = 'zt_num_%02d' % tx
            data[x][y] = 0
        data[x]['month_date'] = month_last_day
        data[x]['month'] = month_view
        data[x]['type'] = x


    #以下两个for语句完成new_rs，old_rs中新增和续建准开发需求放在一条记录中                                                            
        
    for row in yc_rs:
            count,tx,in_type = row
            in_type = str(in_type)
#            print count,tx,in_type
            table_row = 'yc_num_%02d' % tx
            data[in_type][table_row] = count
            data[in_type]['type'] = in_type 
            data[in_type]['kfzx'] = tx      
    for row in yczz_rs:
            count,tx,in_type = row
            table_row = 'yczz_num_%02d' % tx
            data[in_type][table_row] = count
            data[in_type]['type'] = in_type
            data[in_type]['kfzx'] = tx

    for row in zt_rs:
            count,tx,in_type = row
            table_row = 'zt_num_%02d' % tx
            data[in_type][table_row] = count
            data[in_type]['type'] = in_type
            data[in_type]['kfzx'] = tx

            

    #将本月准开发需求汇总数据赋值给total_num


    req_model = get_model('assignment_month_report_kfzx_xmyc')

    #插入数据前，先删除本月已有数据，该语句可以保证该脚本每个月多次跑批
    #db.execute(req_model.delete().where(req_model.c.month_date == month_last_day)) 
    req_model.remove(req_model.c.month_date == month_last_day)
    #print data
    # 按照任务类型存储统计数据
    for t,v in data.items():
        r = req_model(**v)
        r.save()

if __name__ == '__main__':
    try:
        db.begin()
        run()
        db.commit()
        log.info("finish")
    except Exception, e:
        db.rollback()
        log.exception(e)
        log.error('error')
        raise

    
        
    
    

